# author: zhaofeng-shu33
# 统计2018级新生缺失信息
import MySQLdb
import os
import argparse
import pdb
import csv
from program import web_api
from program import utility

if __name__ == '__main__':
    parser = argparse.ArgumentParser()
    parser.add_argument('--update', default=False, type=bool, const=True, nargs='?')
    parser.add_argument('--year', default='2019')
    args = parser.parse_args()
    if args.update:
        session = web_api.login()
    db = MySQLdb.connect(host ="localhost",user="root", db="student", passwd=os.getenv("PASSWORD"), charset="utf8")
    cursor = db.cursor()
    csv_select = ['student_name', 'student_id', 'student_class', 'student_mobilephone',
    'student_group_leader', 'student_mentor_name',
    'student_relative_name', 'student_relative_phone', 'student_house_address']
    csv_missing_detail = [0] * (len(csv_select) + 2) # the last two are mentor phone and pic
    select_fields = ','.join(csv_select)

    cursor.execute('select {0} from qinghua where student_id like "2019%" and student_dormitory like "荷%"'.format(select_fields))
    f = open('output/info_write.csv', 'w')
    csv_header = ['姓名', '学生卡号',
    '专业或班级信息', '照片是否缺失', '学生手机号',
    '工作组组长', '导师', '导师电话',
    '父亲或母亲姓名', '父亲或母亲电话', '家庭住址',
    ]
    f.writelines(','.join(csv_header) + '\n')
    cnt = 0
    missing_cnt = 0
    for item in cursor.fetchall():
        row = []
        item_list = list(item)
        item_list[1] = item_list[1].lstrip('1') # student_id

        mentor_name = item_list[5]


        for index, cell in enumerate(item_list):
            if(cell is None or len(cell) == 0):
                row.append('缺')
                csv_missing_detail[index] += 1
            else:
                row.append(str(cell))

        if(mentor_name is not None and len(mentor_name)>0):
            cursor.execute('select teacher_mobile from qinghua_teacher where teacher_name = "%s"'%mentor_name)
            result = cursor.fetchone()
            if(result is None or len(result) == 0 ):
                row[5] += ',缺'
                csv_missing_detail[-2] += 1
            else:
                row[5] += ',' + result[0]
        else:
            row[5] += ','

        file_name_bmp = os.path.join('verification_example','img_data', item_list[1] + '.bmp')
        file_name_jpg = file_name_bmp.replace('.bmp', '.jpg')
        if(os.path.exists(file_name_bmp) or os.path.exists(file_name_jpg)):
            row[2] += ','
        else:
            row[2] += ',缺'
            csv_missing_detail[-1] += 1
        content = ','.join(row) + '\n'
        cnt += 1
        if(content.find('缺') >= 0):
            missing_cnt += 1
            if args.update:
                student_info_dic = web_api.query(session, item_list[1]) # second param: student_id
                if(student_info_dic != {}):
                    utility.update(cursor, student_info_dic)                    
            f.writelines(content)

    print("Statistics: ")
    print(args.year + " 级新生共有 %d 人住荷园"%cnt)
    print("其中 %d 人信息不完整"%missing_cnt)
    print("详细信息为：")
    csv_select_chinese = ['姓名', '学生卡号',
    '专业或班级信息', '学生手机号',
    '工作组组长', '导师',
    '父亲或母亲姓名', '父亲或母亲电话', '家庭住址',
    '导师电话',
    '照片']
    for index, field in enumerate(csv_select_chinese):
        print("%s 缺失数为 %d"%(field, csv_missing_detail[index]))
